﻿using ImperialServer.Common.Entities;
using ImperialServer.Common.Resources;
using ImperialServer.Data.Interfaces;
using ImperialUtilities.Exceptions;
using ImperialUtilities.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ImperialServer.Data.DAO
{
    public class RegistryAccess : IRegistryAccess
    {
        private string _dbConnectionString;
        private EventViewer _logger;

        /// <summary>
        /// constructor de la clase
        /// </summary>
        public RegistryAccess()
        {
            _dbConnectionString = string.Empty;
            _logger = new EventViewer("ImperialServer.Data.DAO.RegistryAccess");
        }

        /// <summary>
        /// constructor de la clase
        /// </summary>
        /// <param name="connectionString">parameto de conexion</param>
        public RegistryAccess(string connectionString)
        {
            _dbConnectionString = connectionString;
            _logger = new EventViewer("ImperialServer.Data.DAO.RegistryAccess");
        }

        /// <summary>
        /// Metodo que realiza la insercion en la base de datos de un ciudadano nuevo
        /// </summary>
        /// <param name="newCitizen">objeto con todos los datos a insertar</param>
        /// <returns>true si fue exitoso, false si no lo fue</returns>
        public bool InsertNewCitizen(CitizenEntity newCitizen)
        {
            bool successful = false;
            string insertIntoUserQuery = Queries.insertIntoUserQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(insertIntoUserQuery, connection);

                    command.Parameters.Add("@username", System.Data.SqlDbType.NVarChar).Value = newCitizen.Username;
                    command.Parameters.Add("@realname", System.Data.SqlDbType.NVarChar).Value = newCitizen.Realname;
                    command.Parameters.Add("@fkRole", System.Data.SqlDbType.Int).Value = newCitizen.Role.Id;
                    command.Parameters.Add("@fkSpecie", System.Data.SqlDbType.Int).Value = newCitizen.Specie.Id;

                    newCitizen.Id = int.Parse(command.ExecuteScalar().ToString());
                    connection.Close();

                    if (newCitizen.Id > 0)
                    {
                        connection.Close();
                        if (InsertNewUserStatus(newCitizen))
                        {
                            successful = true;
                            _logger.WriteEventViewerLog("InsertNewCitizen", ExceptionMessages.successMessageInfo, false);
                        }
                    }
                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("InsertNewCitizen", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("InsertNewCitizen", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }

        /// <summary>
        /// Metodo que realiza la insercion en la base de datos de un rol nuevo
        /// </summary>
        /// <param name="newRole">objeto con todos los datos a insertar</param>
        /// <returns>true si fue exitoso, false si no lo fue</returns>
        public bool InsertNewRole(RoleEntity newRole)
        {
            bool successful = false;
            string insertIntoRoleQuery = Queries.insertIntoRoleQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(insertIntoRoleQuery, connection);

                    command.Parameters.Add("@roleName", System.Data.SqlDbType.NVarChar).Value = newRole.RoleName;

                    if (command.ExecuteNonQuery() > 0)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("InsertNewRole", ExceptionMessages.successMessageInfo, false);
                    }

                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("InsertNewRole", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("InsertNewRole", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }

        /// <summary>
        /// Metodo que realiza la insercion en la base de datos de una especie nueva
        /// </summary>
        /// <param name="newSpecie">objeto con todos los datos a insertar</param>
        /// <returns>true si fue exitoso, false si no lo fue</returns>
        public bool InsertNewSpecie(SpecieEntity newSpecie)
        {
            bool successful = false;
            string insertIntoSpecieQuery = Queries.insertIntoSpecieQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(insertIntoSpecieQuery, connection);

                    command.Parameters.Add("@specieName", System.Data.SqlDbType.NVarChar).Value = newSpecie.SpecieName;

                    if (command.ExecuteNonQuery() > 0)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("InsertNewSpecie", ExceptionMessages.successMessageInfo, false);
                    }

                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("InsertNewSpecie", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("InsertNewSpecie", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }

        /// <summary>
        /// Metodo que realiza la insercion en la base de datos de un estatus nuevo
        /// </summary>
        /// <param name="newStatus">objeto con todos los datos a insertar</param>
        /// <returns>true si fue exitoso, false si no lo fue</returns>
        public bool InsertNewStatus(StatusEntity newStatus)
        {
            bool successful = false;
            string insertIntoStatusQuery = Queries.insertIntoStatusQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(insertIntoStatusQuery, connection);

                    command.Parameters.Add("@statusName", System.Data.SqlDbType.NVarChar).Value = newStatus.StatusName;

                    if (command.ExecuteNonQuery() > 0)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("InsertNewStatus", ExceptionMessages.successMessageInfo, false);
                    }

                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("InsertNewStatus", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("InsertNewStatus", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }


        /// <summary>
        /// Metodo que realiza la consulta de todos los ciudadanos registrados
        /// </summary>
        /// <returns>tabla con los registros de los ciudadanos</returns>
        public DataTable SelectAllCitizens()
        {
            SqlDataAdapter sqlDataAdapter;
            SqlCommandBuilder commandBuilder;

            SqlCommand sqlCommand = new SqlCommand();
            DataTable tableUsers = new DataTable();

            string query = Queries.selectAllCitizens.ToString();
            try
            {
                sqlCommand.Connection = new SqlConnection(_dbConnectionString);
                sqlCommand.CommandText = query.ToString();

                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                commandBuilder = new SqlCommandBuilder(sqlDataAdapter);

                sqlDataAdapter.Fill(tableUsers);
                _logger.WriteEventViewerLog("SelectAllCitizens", ExceptionMessages.successMessageInfo, false);
            }
            catch (Exception e)
            {
                _logger.WriteEventViewerLog("SelectAllCitizens", e.Message, true);
                throw new ImperialException(ExceptionMessages.generalMessageError, e);
            }
            finally
            {
                sqlCommand.Connection.Close();
                _logger.WriteEventViewerLog("SelectAllCitizens", ExceptionMessages.exitMessageInfo, false);
            }
            return tableUsers;
        }

        /// <summary>
        /// Metodo que realiza la consulta de todos los roles registrados
        /// </summary>
        /// <returns>tabla con los registros de los roles</returns>
        public DataTable SelectAllRoles()
        {
            SqlDataAdapter sqlDataAdapter;
            SqlCommandBuilder commandBuilder;

            SqlCommand sqlCommand = new SqlCommand();
            DataTable tableRoles = new DataTable();

            string query = Queries.selectAllRoles.ToString();
            try
            {
                sqlCommand.Connection = new SqlConnection(_dbConnectionString);
                sqlCommand.CommandText = query.ToString();

                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                commandBuilder = new SqlCommandBuilder(sqlDataAdapter);

                sqlDataAdapter.Fill(tableRoles);
                _logger.WriteEventViewerLog("SelectAllRoles", ExceptionMessages.successMessageInfo, false);
            }
            catch (Exception e)
            {
                _logger.WriteEventViewerLog("SelectAllRoles", e.Message, true);
                throw new ImperialException(ExceptionMessages.generalMessageError, e);
            }
            finally
            {
                sqlCommand.Connection.Close();
                _logger.WriteEventViewerLog("SelectAllRoles", ExceptionMessages.exitMessageInfo, false);
            }
            return tableRoles;
        }

        /// <summary>
        /// Metodo que realiza la consulta de todos las especies registrados
        /// </summary>
        /// <returns>tabla con los registros de las especies</returns>
        public DataTable SelectAllSpecies()
        {
            SqlDataAdapter sqlDataAdapter;
            SqlCommandBuilder commandBuilder;

            SqlCommand sqlCommand = new SqlCommand();
            DataTable tableSpecies = new DataTable();

            string query = Queries.selectAllSpecies.ToString();
            try
            {
                sqlCommand.Connection = new SqlConnection(_dbConnectionString);
                sqlCommand.CommandText = query.ToString();

                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                commandBuilder = new SqlCommandBuilder(sqlDataAdapter);

                sqlDataAdapter.Fill(tableSpecies);
                _logger.WriteEventViewerLog("SelectAllSpecies", ExceptionMessages.successMessageInfo, false);
            }
            catch (Exception e)
            {
                _logger.WriteEventViewerLog("SelectAllSpecies", e.Message, true);
                throw new ImperialException(ExceptionMessages.generalMessageError, e);
            }
            finally
            {
                sqlCommand.Connection.Close();
                _logger.WriteEventViewerLog("SelectAllSpecies", ExceptionMessages.exitMessageInfo, false);
            }
            return tableSpecies;
        }

        /// <summary>
        /// Metodo que realiza la consulta de todos los estatus registrados
        /// </summary>
        /// <returns>tabla con los registros de los estatus</returns>
        public DataTable SelectAllStatuses()
        {
            SqlDataAdapter sqlDataAdapter;
            SqlCommandBuilder commandBuilder;

            SqlCommand sqlCommand = new SqlCommand();
            DataTable tableStatus = new DataTable();

            string query = Queries.selectAllStatus.ToString();
            try
            {
                sqlCommand.Connection = new SqlConnection(_dbConnectionString);
                sqlCommand.CommandText = query.ToString();

                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                commandBuilder = new SqlCommandBuilder(sqlDataAdapter);

                sqlDataAdapter.Fill(tableStatus);
                _logger.WriteEventViewerLog("SelectAllStatuses", ExceptionMessages.successMessageInfo, false);
            }
            catch (Exception e)
            {
                _logger.WriteEventViewerLog("SelectAllStatuses", e.Message, true);
                throw new ImperialException(ExceptionMessages.generalMessageError, e);
            }
            finally
            {
                sqlCommand.Connection.Close();
                _logger.WriteEventViewerLog("SelectAllStatuses", ExceptionMessages.exitMessageInfo, false);
            }
            return tableStatus;
        }


        /// <summary>
        /// </summary>
        /// <param name="citizen">objeto con los datos del ciudadano</param>
        /// <returns>true si es exitosa, false si no</returns>
        public bool UpdateCitizen(CitizenEntity citizen)
        {
            bool successful = false;
            string updateUserQuery = Queries.updateUserQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(updateUserQuery, connection);

                    command.Parameters.Add("@idUser", System.Data.SqlDbType.Int).Value = citizen.Id;
                    command.Parameters.Add("@username", System.Data.SqlDbType.NVarChar).Value = citizen.Username;
                    command.Parameters.Add("@realname", System.Data.SqlDbType.NVarChar).Value = citizen.Realname;
                    command.Parameters.Add("@fkRole", System.Data.SqlDbType.Int).Value = citizen.Role.Id;
                    command.Parameters.Add("@fkSpecie", System.Data.SqlDbType.Int).Value = citizen.Specie.Id;
                    if (command.ExecuteNonQuery() > 0)
                    {
                        connection.Close();
                        if (UpdateUserStatus(citizen))
                        {
                            successful = true;
                            _logger.WriteEventViewerLog("UpdateCitizen", ExceptionMessages.successMessageInfo, false);
                        }
                    }
                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("UpdateCitizen", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("UpdateCitizen", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }

        /// <summary>
        /// </summary>
        /// <param name="role">objeto con los datos del rol</param>
        /// <returns>true si es exitosa, false si no</returns>
        public bool UpdateRole(RoleEntity role)
        {
            bool successful = false;
            string updateRoleQuery = Queries.updateRoleQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(updateRoleQuery, connection);

                    command.Parameters.Add("@idRole", System.Data.SqlDbType.Int).Value = role.Id;
                    command.Parameters.Add("@roleName", System.Data.SqlDbType.NVarChar).Value = role.RoleName;
                    if (command.ExecuteNonQuery() > 0)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("UpdateRole", ExceptionMessages.successMessageInfo, false);

                    }
                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("UpdateRole", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("UpdateRole", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }

        /// <summary>
        /// </summary>
        /// <param name="specie">objeto con los datos de la especie</param>
        /// <returns>true si es exitosa, false si no</returns>
        public bool UpdateSpecie(SpecieEntity specie)
        {
            bool successful = false;
            string updateSpecieQuery = Queries.updateSpecieQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(updateSpecieQuery, connection);

                    command.Parameters.Add("@idSpecie", System.Data.SqlDbType.Int).Value = specie.Id;
                    command.Parameters.Add("@specieName", System.Data.SqlDbType.NVarChar).Value = specie.SpecieName;
                    if (command.ExecuteNonQuery() > 0)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("UpdateSpecie", ExceptionMessages.successMessageInfo, false);

                    }
                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("UpdateSpecie", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("UpdateSpecie", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }

        /// <summary>
        /// </summary>
        /// <param name="status">objeto con los datos del estatus</param>
        /// <returns>true si es exitosa, false si no</returns>
        public bool UpdateStatus(StatusEntity status)
        {
            bool successful = false;
            string updateStatusQuery = Queries.updateStatusQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(updateStatusQuery, connection);

                    command.Parameters.Add("@idStatus", System.Data.SqlDbType.Int).Value = status.Id;
                    command.Parameters.Add("@statusName", System.Data.SqlDbType.NVarChar).Value = status.StatusName;
                    if (command.ExecuteNonQuery() > 0)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("UpdateStatus", ExceptionMessages.successMessageInfo, false);

                    }
                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("UpdateStatus", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("UpdateStatus", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }


        /// <summary>
        /// Metodo que realiza la insercion en la base de datos de un nuevo estatus para un ciudadano
        /// </summary>
        /// <param name="status">objeto con la data del nuevo estatus</param>
        /// <returns>true si es exitosa, false si no</returns>
        private bool InsertNewUserStatus(CitizenEntity citizen)
        {
            bool successful = false;
            string insertIntoUserStatusQuery = Queries.insertIntoUserStatusQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(insertIntoUserStatusQuery, connection);

                    command.Parameters.Add("@fkUser", System.Data.SqlDbType.Int).Value = citizen.Id;
                    command.Parameters.Add("@fkStatus", System.Data.SqlDbType.Int).Value = citizen.UserStatus[0].Status.Id;
                    command.Parameters.Add("@initialDate", System.Data.SqlDbType.DateTime).Value = citizen.UserStatus[0].InitialDate;
                    command.Parameters.Add("@finalDate", System.Data.SqlDbType.DateTime).Value = DBNull.Value;

                    if (command.ExecuteNonQuery() > 0)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("InsertNewUserStatus", ExceptionMessages.successMessageInfo, false);
                    }
                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("InsertNewUserStatus", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("InsertNewUserStatus", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }

        /// <summary>
        /// Metodo que actualiza los valores del estatus de un ciudadano
        /// </summary>
        /// <param name="status">objeto con los datos del estadus a actualizar</param>
        /// <returns>true si es exitosa, false si no</returns>
        private bool UpdateUserStatus(CitizenEntity citizen)
        {
            bool successful = false;
            int lastId = 0;
            string selectUserLastStatus = Queries.selectUserLastStatus;
            string updateUserStatus = Queries.updateUserStatusQuery;

            SqlCommand command;

            using (SqlConnection connection = new SqlConnection(_dbConnectionString))
            {
                try
                {
                    connection.Open();
                    command = new SqlCommand(selectUserLastStatus, connection);
                    command.Parameters.Add("@fkUser", System.Data.SqlDbType.Int).Value = citizen.Id;

                    if (int.TryParse(command.ExecuteScalar().ToString(), out lastId) && lastId != citizen.UserStatus[0].Status.Id)
                    {
                        command = new SqlCommand(updateUserStatus, connection);
                        command.Parameters.Add("@finalDate", System.Data.SqlDbType.DateTime).Value = DateTime.Today;
                        command.Parameters.Add("@fkUser", System.Data.SqlDbType.Int).Value = citizen.Id;

                        if (command.ExecuteNonQuery() > 0)
                        {
                            connection.Close();
                            successful = InsertNewUserStatus(citizen);
                        }
                    }
                    else if (lastId == citizen.UserStatus[0].Status.Id)
                    {
                        successful = true;
                        _logger.WriteEventViewerLog("UpdateUserStatus", ExceptionMessages.exitMessageInfo, false);
                    }
                }
                catch (Exception e)
                {
                    _logger.WriteEventViewerLog("UpdateUserStatus", e.Message, true);
                    throw new ImperialException(ExceptionMessages.generalMessageError, e);
                }
                finally
                {
                    connection.Close();
                    _logger.WriteEventViewerLog("UpdateUserStatus", ExceptionMessages.exitMessageInfo, false);
                }
            }
            return successful;
        }
    }
}
